package core;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.Element;

import xml.MyDom4j;

public class StructTransfer {
	private static final String DBTYPE = "DBTYPE";
	private static final String TABLE_NAME = "TABLE_NAME";
	private static final String CREATE_TABLE = "CREATE TABLE ";

	private static final String BEGINFOMAT = "\n" + '(' + "\n";
	private static final String SEPARATE = "," + "\n";
	private static final String ENDFORMAT = "\n" + ")" + "\n";

	public static List<String> TransferStruct(Document doc, String destDbType)
			throws DocumentException, Exception {

		if (doc == null) {
			System.out.println("TransferStruct can't begin");
			return null;
		}
		Element root = MyDom4j.GetRootElement(doc);
		String sourceDbType = root.attributeValue(DBTYPE);

		List<String> sqlCommondList = new ArrayList<String>();

		// String transactionString = "";
		String createTableString = null;
		String createPrimeKeyString = null;

		for (@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = root.elementIterator("TABLE"); tbIt.hasNext();) {
			Element table = tbIt.next();

			String tbName = table.attributeValue(TABLE_NAME);
			createTableString = CreatTable(tbName);

			for (@SuppressWarnings("unchecked")
			Iterator<Element> fdIt = table.elementIterator("FIELD"); fdIt
					.hasNext();) {
				Element field = fdIt.next();
				createTableString = InsertField(createTableString, field,
						sourceDbType, destDbType);
				if (fdIt.hasNext()) {
					createTableString += SEPARATE;
				} else {
					createTableString += ENDFORMAT;
				}
			}

			@SuppressWarnings("unchecked")
			Iterator<Element> pkIt = table.elementIterator("PRIMEKEY");
			if (pkIt.hasNext()) {
				createPrimeKeyString = "ALTER TABLE " + tbName + "\n";
				Element pk = pkIt.next();
				if (!pkIt.hasNext()) {
					createPrimeKeyString += "ADD PRIMARY KEY (" + pk.getText()
							+ ")" + "\n";
				} else {
					createPrimeKeyString += "ADD CONSTRAINT PK_"
							+ tbName.toUpperCase() + " PRIMARY KEY ("
							+ pk.getText();
					while (pkIt.hasNext()) {
						createPrimeKeyString += "," + pkIt.next().getText();
					}
					createPrimeKeyString += ")" + "\n";
				}
			} else {
				createPrimeKeyString = null;
			}
			if (createTableString != null) {
				sqlCommondList.add(createTableString);
			}
			if (createPrimeKeyString != null) {
				sqlCommondList.add(createPrimeKeyString);
			}
		}
		List<String> ForeignKeySqlCommandList = GetForeignKeySqlCommandList(doc);
		sqlCommondList.addAll(ForeignKeySqlCommandList);
		return sqlCommondList;
	}

	protected static Document ChangeDbType(Document doc, String NewDbType) {
		Element root = doc.getRootElement();
		root.attribute(DBTYPE).setValue(NewDbType);
		return doc;
	}

	protected static Document ChangeTableName(Document doc,
			String OldTableName, String NewTableName) {
		Element root = doc.getRootElement();
		@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = root.elementIterator("TABLE");
		while (tbIt.hasNext()) {
			Element table = tbIt.next();
			if (table.attributeValue("TABLE_NAME").equalsIgnoreCase(
					OldTableName)) {
				table.attribute("TABLE_NAME").setValue(NewTableName);
				@SuppressWarnings("unchecked")
				Iterator<Element> fIt = table.elementIterator("FIELD");
				while (fIt.hasNext()) {
					Element field = fIt.next();
					if (field.element("TABLE_NAME").getTextTrim()
							.equalsIgnoreCase(OldTableName)) {
						field.element("TABLE_NAME").setText(NewTableName);
					}
				}
				return doc;
			}
		}
		return doc;
	}

	protected static Document ChangeColumnName(Document doc, String TableName,
			String OldColumnName, String NewColumnName) {
		Element root = doc.getRootElement();
		@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = root.elementIterator("TABLE");
		while (tbIt.hasNext()) {
			Element table = tbIt.next();
			if (table.attributeValue("TABLE_NAME").equalsIgnoreCase(TableName)) {
				@SuppressWarnings("unchecked")
				Iterator<Element> fIt = table.elementIterator("FIELD");
				while (tbIt.hasNext()) {
					Element field = fIt.next();
					if (field.element("COLUMN_NAME").getTextTrim()
							.equalsIgnoreCase(OldColumnName)) {
						field.element("COLUMN_NAME").setText(NewColumnName);
						return doc;
					}
				}
			}
		}
		return doc;
	}

	protected static Document ChangeDataType(Document doc, String sourceDbType,
			String destDbType) throws DocumentException, Exception {
		Element root = doc.getRootElement();
		if (sourceDbType.equalsIgnoreCase(destDbType)) {
			return doc;
		}
		for (@SuppressWarnings("unchecked")
		Iterator<Element> tbIt = root.elementIterator("TABLE"); tbIt.hasNext();) {
			Element table = tbIt.next();
			for (@SuppressWarnings("unchecked")
			Iterator<Element> fdIt = table.elementIterator("FIELD"); fdIt
					.hasNext();) {
				Element field = fdIt.next();
				String type = field.elementText("TYPE_NAME");
				String size = field.elementText("COLUMN_SIZE");
				type = GetDataType(sourceDbType, destDbType, type, size);
				Element typeName = field.element("TYPE_NAME");
				typeName.setText(type);
			}
		}
		return doc;
	}

	private static String CreatTable(String tbName) {
		String ct = CREATE_TABLE + tbName + BEGINFOMAT;
		return ct;
	}

	private static String InsertField(String creatTbString, Element field,
			String sourceDbType, String destDbType) throws DocumentException,
			Exception {

		String bnum = null;
		String typeName = null;

		Element column = null;
		String text = null;

		column = field.element("COLUMN_NAME");
		text = column.getText();
		creatTbString += text + " ";

		column = field.element("TYPE_NAME");
		text = column.getText();
		typeName = text;

		column = field.element("COLUMN_SIZE");
		text = column.getText();
		if (!sourceDbType.equalsIgnoreCase(destDbType)) {
			typeName = GetDataType(sourceDbType, destDbType, typeName, text);
		}
		bnum = GetBraceForm(destDbType, typeName);
		if (bnum.equalsIgnoreCase("0")) {
			creatTbString += typeName;
		}
		if (bnum.equalsIgnoreCase("1")) {
			creatTbString += typeName;
			creatTbString += "(" + text + ")";
		}
		if (bnum.equalsIgnoreCase("2")) {
			creatTbString += typeName;
			creatTbString += "(" + text + ",";
		}

		column = field.element("DECIMAL_DIGITS");
		text = column.getText();
		if (bnum.equalsIgnoreCase("2")) {
			creatTbString += text + ")";
		}

		column = field.element("IS_NULLABLE");
		text = column.getText();
		if (text.equalsIgnoreCase("NO")) {
			creatTbString += " NOT NULL";

		}
		return creatTbString;
	}

	private static String GetDataType(String sourceDbType, String destDbType,
			String typeName, String size) throws DocumentException, Exception {
		// System.out.println(sourceDbType+destDbType);
		// System.out.println(typeName+text);
		if (sourceDbType.equalsIgnoreCase(destDbType)) {
			return typeName;
		} else {
			Document doc = MyDom4j.Read("C:\\Users\\Administrator\\workspace\\Graduate\\xml\\TypeTransferRule.xml");
			Element root = MyDom4j.GetRootElement(doc);
			Element source = root.element(sourceDbType);
			Element dest = source.element(destDbType);
			for (@SuppressWarnings("unchecked")
			Iterator<Element> typeIt = dest.elementIterator(typeName); typeIt
					.hasNext();) {
				Element type = typeIt.next();
				if (type.attributeCount() == 0) {
					typeName = type.getText();
				} else {
					long max = Long.parseLong(type.attributeValue("max"));
					// System.out.println(max);
					long textnum = Long.parseLong(size);
					if (textnum <= max) {
						typeName = type.getText();
						break;
					}
				}
			}
			return typeName;
		}
	}

	private static String GetBraceForm(String destDbType, String Type)
			throws DocumentException, Exception {
		Document doc = MyDom4j.Read("xml/DataType.xml");
		Element root = MyDom4j.GetRootElement(doc);
		Element dataBase = root.element(destDbType);
		Element dataType = dataBase.element(Type);
		String bnum = dataType.attributeValue("bnum");
		// System.out.println(bnum);
		return bnum;
	}

	static void CreateStructXml(Document doc, String fileName)
			throws IOException {
		MyDom4j.CreatXML(doc, null, fileName);
	}

	static List<String> GetForeignKeySqlCommandList(Document doc)
			throws DocumentException, Exception {

		List<String> ForeignKeySqlCommandList = new ArrayList<String>();

		Element root = doc.getRootElement();
		for (@SuppressWarnings("unchecked")
		Iterator<Element> fkIt = root.elementIterator("FOREIGNKEY"); fkIt
				.hasNext();) {
			Element foreignKey = fkIt.next();
			String foreignKeyName = foreignKey.attributeValue("FOREIGNKEYNAME");
			String foreignKeyTableName = foreignKey
					.attributeValue("ForeignKeyTableName");
			String primeKeyTableName = foreignKey
					.attributeValue("PrimeKeyTableName");
			String updateRule = foreignKey.attributeValue("UpdateRule");
			String deleteRule = foreignKey.attributeValue("DeleteRule");
			List<String> foreignKeyColumnNameList = new ArrayList<String>();
			List<String> primeKeyColumnNameList = new ArrayList<String>();

			for (@SuppressWarnings("unchecked")
			Iterator<Element> fkColIt = foreignKey
					.elementIterator("ForeignKeyColumnName"); fkColIt.hasNext();) {
				Element foreignKeyColumnName = fkColIt.next();
				int keSeq = Integer.parseInt(foreignKeyColumnName
						.attributeValue("keySeq"));
				String fkColumnName = foreignKeyColumnName.getText();
				foreignKeyColumnNameList.add(keSeq - 1, fkColumnName);
			}
			for (@SuppressWarnings("unchecked")
			Iterator<Element> pkColIt = foreignKey
					.elementIterator("PrimeKeyColumnName"); pkColIt.hasNext();) {
				Element primeKeyColumnName = pkColIt.next();
				int keSeq = Integer.parseInt(primeKeyColumnName
						.attributeValue("keySeq"));
				String pkColumnName = primeKeyColumnName.getText();
				primeKeyColumnNameList.add(keSeq - 1, pkColumnName);
			}

			String foreignKeyColumnNameListString = null;
			String primeKeyColumnNameListString = null;

			for (int i = 0; i < foreignKeyColumnNameList.size(); i++) {
				if (i == 0) {
					foreignKeyColumnNameListString = foreignKeyColumnNameList
							.get(0);
					primeKeyColumnNameListString = primeKeyColumnNameList
							.get(0);
				} else {
					foreignKeyColumnNameListString += ","
							+ foreignKeyColumnNameList.get(i);
					primeKeyColumnNameListString += ","
							+ primeKeyColumnNameList.get(i);
				}
			}

			String createForeignKeyString = "ALTER TABLE "
					+ foreignKeyTableName + "\n" + "ADD CONSTRAINT "
					+ foreignKeyName + "\n" + "FOREIGN KEY ("
					+ foreignKeyColumnNameListString + ")" + "\n"
					+ "REFERENCES " + primeKeyTableName + "("
					+ primeKeyColumnNameListString + ")" + "\n" + "ON UPDATE "
					+ updateRule + "\n" + "ON DELETE " + deleteRule + "\n";
			ForeignKeySqlCommandList.add(createForeignKeyString);
		}
		return ForeignKeySqlCommandList;
	}

	public static void main(String[] args) throws DocumentException, Exception {
		// Document doc = MyDom4j.Read("XXY_test.XML");
		// doc = ChangeTableName(doc, "table_1", "table_11");
		// doc = ChangeColumnName(doc, "table_11", "id", "Newid");
		// doc = ChangeDataType(doc, "MSSQL", "ORACLE");
		// System.out.println(TransferStruct(doc, "ORACLE"));
		//
		// doc = ChangeDbType(doc, "ORACLE");
		// CreateStructXml(doc,"newXXY_1.xml");
		Document doc = MyDom4j.Read("test/TableStruct.XML");
		List<String> sqlCommondList = TransferStruct(doc, "MSSQL");
		for (int i = 0; i < sqlCommondList.size(); i++) {
			System.out.println(sqlCommondList.get(i));
		}
	}

}
